﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace DB
{
    public class ListCreater<T> where T : new()
    {
        public static List<T> CreateFromDataTable(DataTable dataTable)
        {
            var temp = new List<T>();

            var t = new T();
            Type type1 = t.GetType();
            var properties1 = type1.GetFields();
            var propertyMapped = new List<string>();
            foreach (var property in properties1)
            {
                if (dataTable.Columns.Contains(property.Name))
                    propertyMapped.Add(property.Name);

            }
            foreach (DataRow row in dataTable.Rows)
            {
                var o = new T();
                Type type = o.GetType();
                var properties = type.GetFields();
                foreach (var property in properties)
                {
                    if (propertyMapped.Contains(property.Name))
                    {
                        if (!(row[property.Name] is System.DBNull))
                            property.SetValue(o, row[property.Name]);
                    }
                }
                temp.Add(o);
            }
            return temp;
        }
    }

    #region DB
    public abstract class DbBase
    {
        private readonly DbEngineBase _dbEngineBase;

        protected DbBase(DbEngineBase dbEngineBase)
        {
            _dbEngineBase = dbEngineBase;
        }

        protected DataTable QueryData(string sql, ref string err)
        {
            return _dbEngineBase.QueryData(sql, ref err);
        }
        protected bool Exec(string sql, ref string err)
        {
            return _dbEngineBase.Exec(sql, ref err);
        }
    }
    #endregion

    #region engine
    public abstract class DbEngineBase
    {
        protected readonly string ConnectionString;

        protected DbEngineBase(string connectionString)
        {
            ConnectionString = connectionString;
        }
        public abstract DataTable QueryData(string sql, ref string err);
        public abstract bool Exec(string sql, ref string err);
    }

    public class DbEngineMysql : DbEngineBase
    {
        public DbEngineMysql(string connectionString) : base(connectionString)
        {
        }

        private List<MySqlConnection> listMySqlConnection = new List<MySqlConnection>();
        private MySqlConnection GetSqlConnection()
        {
            foreach (var mySqlConnection in listMySqlConnection)
            {
                if (mySqlConnection.State == ConnectionState.Broken)
                {
                    mySqlConnection.Open();
                    return mySqlConnection;
                }
                if (mySqlConnection.State == ConnectionState.Closed)
                {
                    mySqlConnection.Open();
                    return mySqlConnection;
                }
                if (mySqlConnection.State == ConnectionState.Open)
                {
                    return mySqlConnection;
                }
            }
            var connection = new MySqlConnection();
            connection.ConnectionString = this.ConnectionString;
            connection.Open();
            listMySqlConnection.Add(connection);
            return connection;
        }
        public override DataTable QueryData(string sql, ref string err)
        {
            var connection = GetSqlConnection();
            var command = new MySqlCommand();
            var ds = new DataSet();
            try
            {
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                command.CommandTimeout = 60;
                command.Connection = connection;
                command.CommandText = sql;
                command.CommandType = CommandType.Text;
                adapter.SelectCommand = command;

                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                GC.Collect();
            }
            return ds.Tables[0];
        }
        public override bool Exec(string sql, ref string err)
        {
            var connection = GetSqlConnection();
            var command = new MySqlCommand();
            try
            {
                command.Connection = connection;
                command.CommandText = sql;
                command.CommandType = CommandType.Text;
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }
    }

    public class DbEngineMssql : DbEngineBase
    {
        public DbEngineMssql(string connectionString) : base(connectionString)
        {
        }

        public override DataTable QueryData(string sql, ref string err)
        {
            return null;
        }
        public override bool Exec(string sql, ref string err)
        {
            return true;
        }
    }
    #endregion
}
